
alter proc rpt_AI_BalanceSheet
@date datetime,
@level int
as
begin
	
	declare @firstDate as datetime
	set @firstDate = DATEADD(mm, DATEDIFF(mm, 0, @date), 0)
	declare @lastDate as datetime
	set @lastDate = dateadd(dd,-1,dateadd(mm,datediff(mm,0,@date)+1,0))
	
	declare @firstLM as datetime
	set @firstLM = DATEADD(MM, -1, @firstDate)
	declare @lastLM as datetime
	set @lastLM = DATEADD(MM, -1, @lastDate)

	declare @dateLM as datetime
	set @dateLM = DATEADD(MM, -1, @date)
	
	declare @result table
	(		
		LocalAcctCode nvarchar(100),
		AcctName nvarchar(100),
		AcctFullName nvarchar(100), 
		IsFather int,
		Levels int,
		GroupMask int, 
		GrpLine int,
		FatherNum nvarchar(100)
	)
	insert into @result select ac.AcctCode, ac.AcctName, 
	case ac.Levels when 2 then '     ' when 3 then '          ' when 4 then '                 ' when 5 then '                            ' else '' end + ac.AcctCode + ' - ' + ac.AcctName, 
	0, ac.Levels, ac.GroupMask, ac.GrpLine, ac.FatherNum from OACT ac 
	where ac.GroupMask in (1, 2, 3)

	insert into @result values('ProfitPeriod', 'Profit Period', '          ' + 'Profit Period', 0, 2, 3, 100, '300000000000000')

		-- update is father
	--have child or not
	update @result set
	IsFather = case when (select COUNT(*) from OACT ct where FatherNum = LocalAcctCode  collate SQL_Latin1_General_CP850_CI_AS)>0 then 1 else 0 end

	declare @las table
	(		
		LocalAcctCode nvarchar(100),
		AcctName nvarchar(100),
		AcctFullName nvarchar(100), 
		IsFather int,
		curAmt numeric(19, 2),
		prevAmt numeric(19, 2),
		FatherNum nvarchar(100),
		Levels int
	)
	insert into @las 
	select re.LocalAcctCode, re.AcctName, re.AcctFullName, re.IsFather, 
	case when re.GroupMask=1 then SUM(isnull(T.curAmt, 0)) else - SUM(isnull(T.curAmt, 0)) end curAmt, 
	case when re.GroupMask=1 then Sum(isnull(T.prevAmt, 0)) else - Sum(isnull(T.prevAmt, 0)) end prevAmt, re.FatherNum, re.Levels 
	from @result re
	left join
	(
		select jd.Account, sum(isnull(jd.Debit, 0) - isnull(jd.Credit, 0)) curAmt, 0 prevAmt from JDT1 jd where jd.RefDate <= @date
		group by jd.Account
		union 
		select jd.Account, 0 curAmt, sum(jd.Debit - jd.Credit) prevAmt from JDT1 jd where jd.RefDate <= @dateLM
		group by jd.Account
		
	)T on re.LocalAcctCode = T.Account collate SQL_Latin1_General_CP850_CI_AS
	group by re.LOcalAcctCode, re.AcctName, re.AcctFullName, re.IsFather, re.GroupMask, re.GrpLine, re.FatherNum, re.Levels
	 order by re.GroupMask, re.GrpLine
	 
	 /*Update Profit Period*/
	 update @las set curAmt = (
	 select  sum(jd.credit-jd.debit) curAmt from JDT1 jd
	join OACT ac on ac.AcctCode = jd.Account
	where jd.RefDate <= @date
	and ac.GroupMask in (4,5,6,7,8)),
	prevAmt = (select  sum(jd.credit-jd.debit) curAmt from JDT1 jd
	join OACT ac on ac.AcctCode = jd.Account
	where jd.RefDate <= @dateLM
	and ac.GroupMask in (4,5,6,7,8))
	where LocalAcctCode = 'ProfitPeriod'
	 
	 declare @noOfLevel as int
	 set @noOfLevel =  @level
	 while @noOfLevel <= 5
	 begin
		 update @las set curAmt = isnull(T.curAmt, 0)
		from @las lb join 
		(select lc.FatherNum, Sum(isnull(lc.curAmt, 0)) curAmt from @las lc group by lc.FatherNum) T on T.FatherNum = lb.LocalAcctCode
 
		update @las set prevAmt = isnull(T.prevAmt, 0)
		from @las lb join 
		(select lc.FatherNum, Sum(isnull(lc.prevAmt, 0)) prevAmt from @las lc group by lc.FatherNum) T on T.FatherNum = lb.LocalAcctCode
		
		set @noOfLevel = @noOfLevel + 1 
	 end
	 
	 --print @lastDate
	select * from @las ls where ls.Levels <= @level 

end	

-- rpt_AI_BalanceSheet '01-31-2013', 5

